use krisflyerkb

declare @ppersoncode int, @ppersonlogin varchar(50), @pteam int, @pcreateddate datetime, @psinsales int
declare curNames cursor for
select pk.PPersonCode, pk.PPersonLogin, PTeam, PSinSales, PCreatedDate from tblPersonnelKB pk where pk.PActive = 1  and pk.PCampaign = 'Krisflyer'
And pk.PCreatedDate Is Not Null 
Group By pk.PPersonCode, pk.PPersonLogin, PTeam, PSinSales, PCreatedDate
delete rpt_AgentNotRead
open curNames
fetch next from curNames into @ppersoncode, @ppersonlogin, @pteam, @psinsales, @pcreateddate
while (@@fetch_Status <> -1)
begin

Insert Into rpt_AgentNotRead(PPersonCode, PPersonLogin, ListId, PTeam, PSinsales)
SELECT     @ppersoncode, @ppersonlogin, count(ListID), @pteam, @psinsales
FROM          tblContentList AS L
WHERE      (ReadConfirm = 1) AND 
(L.ListId Not In (Select ListID from tblContentReadConfirmation 
Where ReadByCode = @ppersoncode And ReadByLogin = @ppersonlogin))
AND
((CONVERT(char(8), FirstPublishDate, 112)) > 
(CONVERT(char(8), @pcreateddate, 112))) AND 

CONVERT(varchar(8), L.ExpireDate, 112) > 
CONVERT(varchar(8), GETDATE(), 112)

fetch next from curNames into @ppersoncode, @ppersonlogin, @pteam, @psinsales, @pcreateddate
end
deallocate curNames
select * from rpt_AgentNotRead
--truncate table dbo.rpt_AgentNotRead



SELECT     T.PPersonCode, T.PPersonLogin, P.PDisplayName, T.ListID AS [UnRead Count]
FROM         rpt_AgentNotRead AS T INNER JOIN
                      tblPersonnelKB P ON T.PPersonCode = P.PPersonCode 
Where P.PActive = 1 And P.PCampaign = 'Krisflyer' And T.PSinSales = 0


select @@version

